<?php
namespace Admin\Controller;
use Think\Controller;
use Common\Controller\BaseController;
use Admin\Controller\ContactController;

/**
 * 单位信息管理
 * Class MenmianController
 * @package Admin\Controller
 */
class MenmianController extends BaseController {
    /**
     * 系统管理 -> 系统管理 -> 单位信息维护 -> listview
     */
    public function index(){
        //获取搜索条件
        $filter = $this->fn_GenerateFilter();
        $where = $this->fn_GenerateWhereClause();
        $order = $this->fn_GenerateOrderBy();

        //分页信息
        $pageNum = intval(I("post.page"));
        $pageNum = $pageNum==0?1:$pageNum;
        $page = $this->fn_GetPageInfo($where, $pageNum);
        $this->assign("page", $page);

        //搜索出单位信息
        $limit = ($page['from']-1).",".$page['num'];
        $list = M("menmian")->where($where)->order($order)->limit($limit)->select();
        for($i=0; $i<count($list); $i++){
            $list[$i]["offset"] = $page['from']-1+$i;
            if($list[$i]["sfjf"]=="否"){
                $list[$i]["qfts"] = self::dateDiff(date("Y/m/d"), $list[$i]["contact_jsrq"]);
                $list[$i]["qfje"] = round($list[$i]["qfts"]*$list[$i]["mzj"]/30, 2);
            }
        }
        $this->assign("list", $list);
        $this->assign("tmpl", $this->fn_GetMsgTmpl());

        //设置搜索条件
        $this->assign("filter", $filter);
        $this->display();
    }

    public function ajax_sendMsgBatch()
    {
        $dataIds = I("post.info");
        $code = I("post.code");
        if (!$dataIds || !$code) {
            $this->error("缺少必要参数");
        }

        $menmian = M("menmian")->where(array("id"=>array("in", $dataIds)))->select();
        if(!$menmian){
            $this->error("摇号信息不存在");
        }

        foreach($menmian as $item){
            $params = array("house"=>$menmian['year'], "name"=>$menmian['name']);
            $res = SmsApiController::SendMsg($code, $item['phone'], $params);
            if(!$res['state']){
                \Think\Log::write("短信发送失败：".$res['info']);
            }else{
                \Think\Log::write("短信发送成功：".$res['phone']);
            }
        }
        $this->success("短信发送成功");
    }

    /**
     * AJAX方法，在formview中对发放入住办理短信
     */
    public function ajax_sendMsg()
    {
        $lid = I("post.info");
        $phone = I("post.phone");
        $code = I("post.code");
        if (!$lid || !$phone || !$code) {
            $this->error("缺少必要参数");
        }

        $menmian = M("menmian")->where(array("id"=>$lid))->find();
        if(!$menmian){
            $this->error("欠缴租金信息不存在");
        }

        //发送短信
        $params = array("year"=>$menmian['year'], "name"=>$menmian['name']);
        $res = SmsApiController::SendMsg($code, $phone, $params);
        if(!$res['state']){
            \Think\Log::write("短信发送失败：".$res['info']);
            $this->error($res['info']);
        }else{
            \Think\Log::write("短信发送成功：".$res['phone']);
            $this->success("短信发送成功");
        }
    }

    private function fn_GetMsgTmpl(){
        $where = array("code"=>"7000");
        return M("sms_tpl")->where($where)->find();
    }

    static public function dateDiff($day1, $day2)
    {
        $second1 = strtotime($day1);
        $second2 = strtotime($day2);
        if ($second1 < $second2) {
            $tmp = $second2;
            $second2 = $second1;
            $second1 = $tmp;
        }
        return ($second1 - $second2) / 86400;
    }

    /**
     * 单位信息维护 -> formview
     */
    public function detail(){
        //读取单位信息
        $dataId = I("get.id");
        $menmian = M("menmian")->where(array("id"=>$dataId))->find();
        if($menmian["sfjf"]=="否"){
            $menmian["qfts"] = $this->dateDiff(date("Y/m/d"), $menmian["contact_jsrq"]);
            $menmian["qfje"] = round($menmian["qfts"]*$menmian["mzj"]/30, 2);
        }
        $this->assign("menmian", $menmian);

        //查询条件
        $where = $this->fn_GenerateWhereClause();
        $order = $this->fn_GenerateOrderBy();
        $pageInfo = $this->fn_GetPageInfo($where);

        //页面信息
        $offset = intval(I("get.offset"));
        $nearby = $this->fn_FindNearbyItems($where, $order, $offset);
        $page = array_merge($pageInfo, $nearby);
        $this->assign("page", $page);

        // 设置上下文信息
        $context = $this->fn_GenerateContext($page);
        $this->assign("context", $context);
        $this->assign("tmpl", $this->fn_GetMsgTmpl());

        $contact = array(
            "ksrq" => date('Y/m/d'),
            "jsrq" => date('Y')."/12/31",
            "dibaork" => 0,
            "yj" => $menmian['yj'],
            "size" => $menmian['size'],
            "price" => $menmian['zj'],
            "mprice" => $menmian['mzj'],
        );
        $contact['total'] = $this->fn_CalContactZj($contact, intval(date("m")), intval(date("d")));
        $contact['total'] = round($contact['total'], 2);
        $contact['total2'] = $contact['total']+$contact['yj'];
        $this->assign('contact', $contact);

        $this->display();
    }

    private function fn_GetFormValues(){
        return array(
            "no" => "HT".I("post.ywno"),
            "ywno" => I("post.ywno"),
            "community" => I("post.community"),
            "derate_money" => I("post.derate_money"),
            "derate_type" => I("post.derate_type"),
            "state" => "未签订",
            "type" => I("post.type"),
            "dibaork" => I("post.dibaork"),
            "jiafang" => I("post.jiafang"),
            "yifang" => I("post.yifang"),
            "fwbh" => I("post.fwbh"),
            "fwdz" => I("post.fwdz"),
            "size" => I("post.size"),
            "zj" => floatval(I("post.zj")),
            "mzj" => floatval(I("post.mzj")),
            "total" => floatval(I("post.total")),
            "total2" => floatval(I("post.total2")),
            "yj" => floatval(I("post.yj")),
            "ksrq" => I("post.ksrq"),
            "jsrq" => I("post.jsrq"),
            "jffr" => I("post.jffr"),
            "jfjbr" => I("post.jfjbr"),
            "jfdz" => I("post.jfdz"),
            "jfdh" => I("post.jfdh"),
            "yfsfz" => I("post.yfsfz"),
            "yfdw" => I("post.yfdw"),
            "yfdz" => I("post.yfdz"),
            "yfdh" => I("post.yfdh"),
            "remarks" => I("post.remarks"),
            "cuser" => session("username"),
            "ctime" => I("post.ctime")
        );
    }

    public function ajax_CreateContact(){

        $dataId = I("post.id");
        $menmian = M("menmian")->where(array("id"=>$dataId))->find();
        if(!$menmian){
            $this->error("必要参数传递错误");
        }

        $trans = M();
        $trans->startTrans();   // 开启事务
        try{
            //第一步：创建合同
            $contact = $this->fn_GetFormValues();
            $conId = M("contact")->add($contact);
            if(!$conId){
                $this->error("系统错误，创建合同失败");
            }

            //第二步：添加房源租赁记录
            /*$res = $this->fn_AddHouseRentalLog($contact);
            if (!$res) {
                $this->error("系统错误，房源租赁日志写入失败");
            }*/

            //第三步：设置房源状态
            $res = $this->fn_MarkHouseOccupied($menmian, $contact);
            if (!$res) {
                $this->error("系统错误，房源状态修改失败");
            }

            //第四步：缴纳租金
            $remarks = "签订合同，缴纳租金";
            $zjNo = $this->fn_PayRentalZj($contact, $contact['total'], I("post.ctime"), $remarks);
            if (!$zjNo) {
                $this->error("系统错误，租金缴费记录写入失败");
            }

            //第五步：缴纳押金
            $remarks = "签订合同，缴纳押金";
            $yjNo = $this->fn_PayRentalYj($contact, $contact['yj'], I("post.ctime"), $remarks);
            if (!$yjNo) {
                $this->error("系统错误，押金缴费记录写入失败");
            }

            //第六步：设置申请状态
            $res = $this->fn_MarkMenmianDone($menmian, $contact);
            if(!$res){
                $this->error("系统错误，修改申请状态失败");
            }

            //第七步：设置合同为已签订状态
            $res = $this->fn_MarkContactAssigned($conId, $yjNo);
            if(!$res){
                $this->error("系统错误，修改合同状态失败");
            }

            $trans->commit();   // 提交事务
            $this->success("押金交易流水号：".$yjNo."<br />租金交易流水号：".$zjNo);
        }catch (Exception $e) {
            $trans->rollback();   // 开启事务
            $this->error($e->getMessage());
        }
    }

    protected function fn_MarkContactAssigned($contactId, $payNo){
        $where = array("id"=>$contactId);
        $data = array("pay_no"=>$payNo, "state"=>'已签订', 'qtime'=>date("Y/m/d"));
        return M("contact")->where($where)->save($data);
    }

    private function fn_PayRentalZj($contact, $amount, $payDate, $remarks){
        $payment = array(
            "name" => $this->fn_GeneratePaymentsNo(),
            "ptype" => $contact['type']."租金",
            "contact_no" => $contact['no'],
            "community" => $contact['community'],
            "house_no" => $contact['fwbh'],
            "house_addr" => $contact['fwdz'],
            "yw_no" => $contact['ywno'],
            "amount" => $amount,
            "year" => substr($contact['ksrq'], 0, 4),
            "date1" => $contact['ksrq'],
            "date2" => $contact['jsrq'],
            "month_start" => substr($contact['ksrq'], 5, 2),
            "month_end" => substr($contact['jsrq'], 5, 2),
            "type" => "收", //收，付
            "payer" => $contact['yifang'],
            "payer_idno" => $contact['yfsfz'],
            "pdate" => $payDate,
            "remarks" => $remarks,
            "cuser" => session("username"),
            "ctime" => time()
        );
        if(M("payments")->add($payment)){
            return $payment['name'];
        }
        return null;
    }

    private function fn_PayRentalYj($contact, $amount, $payDate, $remarks){
        $payment = array(
            "name" => $this->fn_GeneratePaymentsNo(),
            "ptype" => $contact['type']."押金",
            "contact_no" => $contact['no'],
            "community" => $contact['community'],
            "house_no" => $contact['fwbh'],
            "house_addr" => $contact['fwdz'],
            "yw_no" => $contact['ywno'],
            "amount" => $amount,
            "type" => "收", //收，付
            "payer" => $contact['yifang'],
            "payer_idno" => $contact['yfsfz'],
            "pdate" => $payDate,
            "remarks" => $remarks,
            "cuser" => session("username"),
            "ctime" => time()
        );
        if(M("payments")->add($payment)){
            return $payment['name'];
        }
        return null;
    }

    protected function fn_MarkHouseOccupied($menmian, $contact){
        $houseData = array(
            "rzdate"=> $contact['ctime'],
            "state" => "租赁中",
            "zhname" => $menmian['name'],
            "zhidno" => $menmian['idno'],
            "jtnum" => $menmian['resident_num'],
            "phone" => $menmian['phone'],
            "yw_no"=> $menmian['no'],
            "contact_no"=> $contact["no"],
            "utime"=> time(),
        );
        return M("house")->where(array("name"=>$menmian["house_no"]))->save($houseData);
    }

    /**
     * 添加房源的租赁记录
     * @param $houseId 房源ID
     * @param $name 租赁人姓名
     * @param $idno 租赁人身份证
     */
    protected function fn_AddHouseRentalLog($contact){
        $rental = array(
            "type"=> $contact['type'],
            "cuser"=> self::$u["username"],
            "ctime"=> date("Y/m/d"),
            "house_no"=> $contact["fwbh"],
            "bzdx"=> $contact['yifang'],
            "idno"=> $contact['yfsfz'],
        );
        return M("house_log")->add($rental);
    }

    private function fn_MarkMenmianDone($menmian, $contact){
        $where = array("id"=>$menmian['id']);
        $data = array(
            "utime" => time(),
            "state" => '申请成功',
            "contact_no" => $contact['no'],
            "contact_date" => $contact['ctime'],
        );
        return M("menmian")->where($where)->save($data);
    }

    public function fn_CalContactZj($contact, $month, $day){
        $month = 12 - $month + max((30-$day),0)/30;
        if($contact['dibaork']*13>=$contact['size']){ //低保人口享受面积大于房屋面积，则不要出房租
            return $contact['size']*1.1*$month;
        }
        //13平米每人内按1.1元每平米的价格计算
        $mzj = ($contact['size']-$contact['dibaork']*13)*$contact['price']/$contact['size'] + $contact['dibaork']*13*1.1;
        return $mzj*$month;
    }

    protected function fn_GetLastYears($num=5){
        $years = array();
        $year = intval(date("Y"));
        for($i=-1; $i<$num; $i++){
            $years[] = $year-$i;
        }
        return $years;
    }

    /**
     * 单位信息维护 -> 新增
     */
    public function add(){
        if(IS_POST){    //添加公司信息
            $data = array(
                "no" => $this->fn_GenerateMenmianNo(I("post.house_type")),
                "community" => I("post.community"),
                "house_no" => I("post.house_no"),
                "house_addr" => I("post.house_addr"),
                "house_type" => I("post.house_type"),
                "name" => I("post.name"),
                "gender" => I("post.gender"),
                "phone" => I("post.phone"),
                "addr" => I("post.addr"),
                "idno" => I("post.idno"),
                "resident_num" => I("post.resident_num"),
                "yj" => I("post.yj"),
                "zj" => I("post.zj"),
                "size" => I("post.size"),
                "mzj" => I("post.mzj"),
                "sfzImg" => I("post.sfzImg"),
                "hkbImg" => I("post.hkbImg"),
                "yyzzImg" => I("post.yyzzImg"),
                "remarks" => I("post.remarks"),
                "cuser" => session("username"),
                "ctime" => date("Y/m/d"),
            );
            $dataId = M("menmian")->add($data);
            if($dataId){
                header("Location:/admin/menmian/detail?id=".$dataId);
            }else{
                $this->error("系统错误，创建申请信息失败");
            }
        }

        //复制信息时处理流程
        if(I("get.id") && I("get.action"=="copy")){
            $menmian = M("menmian")->where(array("id"=>I("get.id")))->find();
            $this->assign("menmian", $menmian);
        }

        $this->assign("years", $this->fn_GetLastYears(5));
        $this->assign("derate_type", ContactController::$derate_type);
        $this->display();
    }

    private function fn_GenerateMenmianNo($house_type){
        $data = array("time"=>date("Y-m-d H:i:s"));
        $no = M("menmian_no")->add($data);
        if($house_type=='商业用房'){
            return "S".$no;
        }else if($house_type=='直管公房'){
            return "Z".$no;
        }
        return null;
    }

    /**
     * 房屋自动补全搜索算法
     */
    public function ajax_SearchHouseByAddr(){
        $h = array();
        $where = array(
            "h.state"=>"闲置中",
            "h.type"=>array("in", array("直管公房","商业用房")),
            "h.addr"=>array("like", "%".I("get.house_addr")."%")
            );
        $house = M("house h")->where($where)->field("h.*, c.name community")
            ->join(C("DB_PREFIX")."community c on c.id = h.community_id", "LEFT")
            ->limit(10)->select();
        //$this->error(M()->getLastSql());
        foreach ($house as $v){
            array_push($h, array("value"=>$v["addr"], "data"=>$v));
        }
        $this->ajaxReturn(array('query'=>I("get.house_addr"), 'suggestions' => $h));
    }

    /**
     * AJAX方法，listview/formview 缴纳租金
     */
    public function ajax_payRental(){

        $id = I("post.id");
        if(!$id){
            $this->error("操作失败，参数缺少");
        }

        $trans = M();
        $trans->startTrans();   // 开启事务
        try {
            $menmian = M("menmian c")->where(array("id"=>$id))->find();
            if(!$menmian){
                $this->error("操作失败2，参数缺少");
            }
            //增加一条缴费记录
            $data = I("post.data");
            $pno = $this->fn_PayRentalZj($data, $menmian);
            if(!$pno){
                $this->error("操作失败，支付日志写入错误");
            }
            //修改缴费状态
            $info = array("state" => "已缴", "pay_date" => $data['pay_date'], "pay_no"=>$pno);
            $res = M("menmian")->where(array("id"=>$id))->save($info);
            if(!$res){
                $this->success("租金缴纳交易票号：".$pno);
            }

            $trans->commit();   // 提交事务
            $this->success("租金缴纳交易票号：".$pno);
        } catch (Exception $e) {
            $trans->rollback();   // 开启事务
            $this->error($e->getMessage());
        }
    }

    private function fn_GeneratePaymentsNo(){
        $data = array("time"=>date("Y-m-d H:i:s"));
        $no = M("payments_no")->add($data);
        return "P".$no;
    }

    /**
     * AJAX方法，在listview中进行批量删除信息
     */
    public function ajax_del(){

        $dataIds = I("post.info");
        if(count($dataIds)==0){
            $this->error("您还没有选中任何记录");
        }

        $draftMenmian = array();
        $map["id"] = array("in", $dataIds);
        $menmian = M("menmian")->where($map)->select();
        foreach($menmian as $item){
            if($item['state']!='待签订合同'){
                $this->error("商铺、直管公房:".$item['house_addr']."已申请成功，无法删除");
            }else{
                $draftMenmian[] = $item['id'];
            }
        }

        $map["id"] = array("in", $draftMenmian);
        $res = M("menmian")->where($map)->delete();
        if($res){
            $this->success("操作成功");
        }else{
            $this->error("系统错误");
        }
    }

    /**
     * AJAX方法，在formview中进行单条信息删除
     */
    public function ajax_del2(){
        $id = I("post.id");
        $where = array("id"=>$id);
        $menmian = M("menmian")->where($where)->find();
        if($menmian){
            $this->error("系统错误");
        }
        if($menmian['state']!='待签订合同'){
            $this->error("商铺、直管公房申请已生效，无法删除");
        }

        //根据ID删除单位记录
        $res = M("menmian")->where(array("id" => $id))->delete();
        if(!$res){
            $this->error("系统错误");
        }

        //分页信息
        $where = $this->fn_GenerateWhereClause();
        $order = $this->fn_GenerateOrderBy();
        $total = $this->fn_GetTotalItems($where);
        if($total==0){
            $this->success(null);
        }

        //计算下一条记录的offset
        $offset = intval(I("post.offset"));
        $offset = ($offset>=$total-1)?0:$offset;

        //获取下一记录的信息
        $nextItem = $this->fn_GetInfoByOffset($offset, $where, $order);
        $this->success($nextItem["id"]); //返回下一条记录的ID
    }

    /**
     * AJAX方法，通过ID来修改信息
     */
    public function ajax_UpdateInfoById(){
        $data = I("post.data");
        $dataId = I("post.id");
        $res = M("menmian")->where(array("id"=>$dataId))->save($data);
        if($res){
            $this->success("0k, 200");
        }else{
            $this->error("更新异常，请稍候再试");
        }
    }

    /**
     *  辅助方法，通过$_POST的值来生成where查询条件
     */
    private function fn_GenerateWhereClause(){
        $where = array();
        if(I("request.value")){
            $where[I("request.field")] = array("like", "%".I("request.value")."%");
        }
        return $where;
    }

    /**
     *  辅助方法，通过$_POST的值来生成order by子句
     */
    private function fn_GenerateOrderBy(){
        $order = "id desc";
        if(I("request.order_field") && I("request.order_sort")){
            $order = I("request.order_field") . " " . I("request.order_sort");
        }
        return $order;
    }

    /**
     *  辅助方法，通过$_POST的值来生成Filter (listview)
     */
    private function fn_GenerateFilter(){
        if(IS_POST){
            return array(
                "page" => I("post.page"),
                "field" => I("post.field"),
                "value" => I("post.value"),
                "order_field" => I("post.order_field"),
                "order_sort" => I("post.order_sort"),
            );
        }
        return array(
            "page" => 1,
            "order_field" => "id",
            "order_sort" => "desc",
        );
    }

    /**
     * 辅助方法，通过$_POST的值来生成Context (formvier)
     * @return array
     */
    private function fn_GenerateContext($page){
        return array(
            "id" => I("request.id"),
            "field" => I("request.field"),
            "value" => I("request.value"),
            "order_field" => I("request.order_field"),
            "order_sort" => I("request.order_sort"),
            "page" => I("request.page"),
            "offset" => I("request.offset"),
            "index" => I("get.offset")-($page["curPage"]-1)*$page["num"]+1
        );
    }

    /**
     * 辅助方法，通过where,order,offset来查询信息
     * @param $offset
     * @param $where
     * @param $order
     * @return mixed
     */
    private function fn_GetInfoByOffset($offset, $where, $order){
        $limit = $offset.",1";
        $res =  M("menmian")->field("id,name")->where($where)->order($order)->limit($limit)->select();
        if($res){
            return $res[0];
        }
        return array();
    }

    /**
     * 辅助方法，通过where, curpage, num来生成分页信息
     * @param $where
     * @param int $curPage
     * @param int $num
     * @return array
     */
    private function fn_GetPageInfo($where, $curPage=1, $num=15){
        $total = M("menmian")->where($where)->count();
        $totalPage = ceil($total/$num);
        $nextPage = $curPage==$totalPage?1:($curPage+1);
        $prevPage = $curPage==1?$totalPage:($curPage-1);
        $to = $curPage*$num;
        $res = array(
            "total" => $total,
            "num" => $num,
            "from" => (($curPage-1)*$num+1),
            "to" => $to>$total?$total:$to,
            "curPage" => $curPage,
            "nextPage" => $nextPage,
            "prevPage" => $prevPage
        );
        return $res;
    }

    /**
     * 辅助方法，通过where来统计总的信息数量
     * @param array $where
     * @return mixed
     */
    public function fn_GetTotalItems($where=array()){
        return M("menmian")->where($where)->count();
    }

    /**
     * 辅助方法，通过where, order, offset查询相邻两条数据
     * @param $where
     * @param $offset
     */
    public function fn_FindNearbyItems($where, $order, $offset){
        $total = M("menmian")->where($where)->count();
        $prevOffset = $offset>0?$offset-1:$total-1;
        $nextOffset = $offset>=$total-1?0:$offset+1;
        $prevItem = $this->fn_GetInfoByOffset($prevOffset, $where, $order);
        $nextItem = $this->fn_GetInfoByOffset($nextOffset, $where, $order);
        return array(
            "prev" => array("id"=>$prevItem["id"],"offset"=>$prevOffset),
            "next" => array("id"=>$nextItem["id"],"offset"=>$nextOffset),
        );
    }

    /**
     * 导出摇号中奖名单：
     * 1.如果选中了某些行，则导出所选中的项；
     * 2.如果没有选中任意行，则导出所有的项；
     */
    public function export(){
        $where = array("state"=>"未缴");
        $ids = I("get.ids");
        if($ids){
            $where = "id in (".$ids.")";
        }else if(I("get.field") & I("get.value")){
            $where[I("get.field")] = array("like", "%".I("get.value")."%");
        }
        $this->assign("where", serialize($where));
        $this->display();
    }

    /**
     * 导出公示名单
     * @throws \Exception
     */
    public function ajax_Exportmenmian(){
        $where = unserialize( html_entity_decode(I("post.where")) ); //"被转换成了html实体，需要decode
        $data = M("menmian")->where($where)->order("house_no asc")->select();
        $fileName = $this->fn_ExportmenmianExcel($data);
        $this->success($fileName);
    }

    public function fn_ExportmenmianExcel($data){
        import("Common.ORG.Util.PHPExcel");
        import("Common.ORG.Util.PHPExcel.Writer.Excel5");
        import("Common.ORG.Util.PHPExcel.IOFactory.php");

        $inputFileName = "./db/xlsx_tpl/menmian.xls";
        try { //  Read your Excel workbook
            $inputFileType = \PHPExcel_IOFactory::identify($inputFileName);
            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($inputFileName);
        } catch(Exception $e) {
            die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
        }

        $rowCount = 3; //从第2行开始写数据
        for($i=0; $i<count($data); $i++){
            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, " ".$data[$i]["house_no"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, " ".$data[$i]["name"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, " ".$data[$i]["idno"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, " ".$data[$i]["phone"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, " ".$data[$i]["year"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount, " ".$data[$i]["total"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('G'.$rowCount, "年审".$data[$i]["state_ns"]);
            $rowCount += 1;
        }

        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $newFile = '/db/export/'.time().'.xlsx';
        $objWriter->save(".".$newFile);
        return $newFile;
    }

}